package database;

import hypeerweb.*;

import java.sql.*;
import java.util.*;

import proxyCommand.*;

/**
 * The HyPeerWebDatabase deals mainly in querying for connections
 * and saving the hypeerweb to a file.
 * 
 * Includes 5 tables, neighbor, sneighbor, fold, sfold, and webids.
 * All but the webid have two columns fromId and toId which are of the type integer.
 * The webids table just has one row of type integer webid.
 */
public class HyPeerWebDatabase {
	/**
	 * Neighbor Table.
	 */
	private static final String SMALLER_WITH_TABLE = "smallerwith";
	private static final String SMALLER_WITHOUT_TABLE = "smallerwithout";
	private static final String LARGER_TABLE = "larger";

	/**
	 * Surrogate Neighbor Table.
	 */
	private static final String SURROGATE_NEIGHBOR_TABLE = "sneighbor";
	private static final String INVERSE_SURROGATE_NEIGHBOR_TABLE = "inverseneighbor";
	/**
	 * Fold Table.
	 */
	private static final String FOLD_TABLE = "fold";

	/**
	 * Surrogate Fold Table.
	 */
	private static final String SURROGATE_FOLD_TABLE = "sfold";
	private static final String INVERSE_SURROGATE_FOLD_TABLE = "ifold";

	/**
	 * The default name of the file  that holds the sqlite3  database.
	 */
	private static final String DEFAULT_DATABASE_NAME = "default.db";

	/**
	 * Connection to SQL file.
	 */
	private static Connection dbConnection;

	/**
	 * The Singleton variable to ensure that there will never be more than
	 * one HyPeerWebDatabase.
	 */
	private static HyPeerWebDatabase hypeerwebDB;

	/**
	 * The filename of the currently selected SQL file.
	 */
	private static String dbFileName;

	/**
	 * Can only be called by the initHyPeerWebDatabase method.
	 *
	 * @param dbName the name of the SQL file to be loaded
	 */
	private HyPeerWebDatabase(String dbName) {
		dbFileName = dbName;
		loadTables();
	}

	/**
	 * @return the static instance of HyPeerWebDatabase
	 *
	 * @pre initHyPeerWebDatabase() must have been called previously. This
	 *      ensures the singleton � null.
	 * @post result = singleton
	 */
	public static HyPeerWebDatabase getSingleton() {
		return hypeerwebDB;
	}

	public static void initHyPeerWebDatabase() {
		initHyPeerWebDatabase("");
	}
	
	/**
	 * @param dbName
	 *            The name of the database
	 *
	 * @pre dbName = NULL, or dbName = "", or there must already exist a
	 *      database with the given name.
	 * @post IF dbName = null OR |dbName| = 0 new
	 *       HyPeerWebDatabase(DEFAULT_DATABASE_NAME).postCondition ELSE new
	 *       HyPeerWebDatabase(dbName).postCondition
	 *
	 */
	public static void initHyPeerWebDatabase(String dbName) {
		String fileName = dbName;
		if (dbName == null || dbName.equals("")) {
			fileName = DEFAULT_DATABASE_NAME;
		}
		hypeerwebDB = new HyPeerWebDatabase(fileName);
	}

	/**
	 * @param dbName
	 *            The name of the database
	 *
	 * @pre dbName = NULL, or dbName = "", or a database will be saved
	 * 		with thegiven name.
	 * 
	 * @post IF dbName = null OR |dbName| = 0
	 *       database.saveDatabase(DEFAULT_DATABASE_NAME).postCondition ELSE
	 *       database.saveDatabase(dbName).postCondition
	 *
	 */
	public static void saveHyPeerWebDatabase(String dbName) {
		try {
			if (dbName == null || dbName.equals("")) {
				dbFileName = DEFAULT_DATABASE_NAME;
			}
			else {
				dbFileName = dbName;
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Will load the nodes stored in the database and put them in the object database.
	 * 
	 * @pre none
	 * @post All nodes that were stored when the segment was shut down are in the ObjectDB and the connections are correct
	 * 
	 */
	public static void loadSegment() {
		HashMap<Integer,Node> nodes = getAllWebIds();

		for(Node node:nodes.values()) {
			if(!(node instanceof NodeProxy)) {
				//we get it's connections and set them up accordingly correctly
				int webId = node.getWebId().getValue();
				HashSet<Integer> smallerWithNeighbors = getSetWithInfo(SMALLER_WITH_TABLE, "fromId", webId, "toId");
				for(Integer val: smallerWithNeighbors) {
					Neighbor nD = new Neighbor(nodes.get(val));
					node.addSmallerWithNeighbor(nD);
				}
				HashSet<Integer> smallerWithoutNeighbors = getSetWithInfo(SMALLER_WITHOUT_TABLE, "fromId", webId, "toId");
				for(Integer val: smallerWithoutNeighbors) {
					Neighbor nD = new Neighbor(nodes.get(val));
					node.addSmallerWithoutNeighbor(nD);
				}
				HashSet<Integer> largerNeighbors = getSetWithInfo(LARGER_TABLE, "fromId", webId, "toId");
				for(Integer val: largerNeighbors) {
					Neighbor nD = new Neighbor(nodes.get(val));
					node.addLargerNeighbor(nD);
				}

				HashSet<Integer> surrogateNeighbors = getSetWithInfo(SURROGATE_NEIGHBOR_TABLE, "fromId", webId, "toId");
				for(Integer val: surrogateNeighbors) {
					DownPointer nD = new DownPointer(nodes.get(val));
					node.addDownPointerDB(nD);
				}

				HashSet<Integer> inverseSurrogateNeighbors = getSetWithInfo(INVERSE_SURROGATE_NEIGHBOR_TABLE, "fromId", webId, "toId");
				for(Integer val: inverseSurrogateNeighbors) {
					UpPointer nD = new UpPointer(nodes.get(val));
					node.addUpPointerDB(nD);
				}	
				
				int fold = HyPeerWebDatabase.getSingleWithInfo(FOLD_TABLE, "fromId", webId, "toId");
				if(fold >= 0 ) {
					node.setFold(nodes.get(fold));
				}
				else{
					node.setFold(null);
				}
				
				int surrogateFold = HyPeerWebDatabase.getSingleWithInfo(SURROGATE_FOLD_TABLE, "fromId", webId, "toId");
				if(surrogateFold >= 0) {
					System.out.println("Setsurrogatefold: "+nodes.get(surrogateFold).getWebId());

					node.setNewSurrogateFold(nodes.get(surrogateFold));
				}
				
				int inverseSurrogateFold = HyPeerWebDatabase.getSingleWithInfo(INVERSE_SURROGATE_FOLD_TABLE, "fromId", webId, "toId");
				if(inverseSurrogateFold >= 0) {
					System.out.println("Setinversefold: "+nodes.get(inverseSurrogateFold).getWebId());

					node.setInverseSurrogateFold(nodes.get(inverseSurrogateFold));
				}
			}
		}
	}
	private static HashMap<Integer,Node> getAllWebIds()  {
		HashMap<Integer,Node> toReturn = new HashMap<Integer,Node>();
		//create the select statement:
		String query = "SELECT * FROM webids;"; 
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:" + dbFileName);
			Statement stat = dbConnection.createStatement();

			ResultSet rs = stat.executeQuery(query);
			while (rs.next()) {
				//check if we have a real node
				if(!rs.getBoolean(2)) {
					Node node = new Node(rs.getInt(1),new LocalObjectId(rs.getInt(3)));
					System.out.println("Created Node: "+rs.getInt(1));
					toReturn.put(rs.getInt(1), node);
				}
				else {
					//we have a proxy
					//(webId integer, isProxy boolean, locobjID integer, ipAddress string, port integer);");
					System.out.println("IP address: " + rs.getString("ipAddress"));
					NodeProxy np = new NodeProxy(new GlobalObjectId(rs.getString("ipAddress"),new PortNumber(rs.getInt("port")),new LocalObjectId(rs.getInt(3))));
					np.changeWebId(new WebId(rs.getInt(1)));
					toReturn.put(rs.getInt(1), np);
					System.out.println("Created NodeProxy : "+rs.getInt(1));

				}

			}
		
			rs.close();
			dbConnection.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return toReturn;
	}

	
	/**
	 *
	 * @param webId
	 *            The webId of the node whose information we are going to
	 *            retrieve.
	 *            
	 * @return A SimplifiedNodeDomain with the pertinent information about
	 * 		   the given webId's relatives.
	 * 
	 * @throws Exception if there is no node with the given webId
	 *
	 * @pre There exists a node in the database with the given webId.
	 * 
	 * @post Result contains the webId, neighbors, upPointers, downPointers,
	 *       fold, surrogateFold, and inverse surrogate fold of the
	 *       indicated node.
	 *       
	 */
	/*public SimplifiedNodeDomain getNode(int webId) throws Exception {
		if (!this.webIdExists(webId)) {
			throw new Exception();
		}
		WebId webid = new WebId(webId);
		// this will be constant
		int height = webid.getHeight();

		HashSet<Integer> neighbors = getSetWithInfo(NEIGHBOR_TABLE, "fromId", webId, "toId");

		HashSet<Integer> surrogateNeighbors = getSetWithInfo(SURROGATE_NEIGHBOR_TABLE, "fromId", webId, "toId");
		HashSet<Integer> inverseSurrogateNeighbors = getSetWithInfo(SURROGATE_NEIGHBOR_TABLE, "toId", webId, "fromId");
		int fold = this.getSingleWithInfo(FOLD_TABLE, "fromId", webId, "toId");
		int surrogateFold = this.getSingleWithInfo(SURROGATE_FOLD_TABLE, "fromId", webId, "toId");
		int inverseSurrogateFold = this.getSingleWithInfo(SURROGATE_FOLD_TABLE, "toId", webId, "fromId");


		SimplifiedNodeDomain n = new SimplifiedNodeDomain(webId, height,
				neighbors, surrogateNeighbors, inverseSurrogateNeighbors,
				fold, surrogateFold, inverseSurrogateFold, 0);
		//System.out.println(n.toString());
		return n;
	}
*/

	/**
	 * Returns the webid for the connection requested.
	 *
	 * @param table 
	 * 		The table name to get the information from
	 * 
	 * @param key
	 * 		The column name that you are looking for, either fromID or toId
	 * @param webId
	 * 		The node's webid whos connections you are looking for
	 * @param column
	 * 		The column row to extract the information
	 * 
	 * @pre none
	 * @post none
	 * 
	 * @return A hash set of values stored in the column parameter name's
	 *  column from the sqlite statement: select * from "table" where ( "key" = "webid")
	 * 
	 */
	public static HashSet<Integer> getSetWithInfo(String table,String key, int webId, String column) {
		HashSet<Integer> toReturn = new HashSet<Integer>();
		String query = "SELECT * FROM " + table + " WHERE ( " + key + " = " + webId + ");";
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:"
					+ dbFileName);
			Statement stat = dbConnection.createStatement();

			ResultSet rs = stat.executeQuery(query);

			while (rs.next()) {
				toReturn.add(rs.getInt(column));
			}
			rs.close();
			dbConnection.close();
			return toReturn;
		}
		catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	

	/**
	 * Returns the webid for the connection requested.
	 *
	 * @param table 
	 * 		The table name to get the information from
	 * 
	 * @param key
	 * 		The column name that you are looking for, either fromID or toId
	 * @param webId
	 * 		The nodes webid whos connections you are looking for
	 * @param column
	 * 		The column row to extract the information
	 * 
	 * @pre none
	 * @post the FIRST value stored in the column parameter name from the sqlite statement: select * from "table" where ( "key" = "webid").
	 * 
	 */
	public static int getSingleWithInfo(String table,String key, int webId, String column) {
		int toReturn = -1;
		// create the select statement:
		String query = "SELECT * FROM " + table + " WHERE ( " + key + " = " + webId + ");";
		;System.out.println("Query= "+query);
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:" + dbFileName);
			Statement stat = dbConnection.createStatement();

			ResultSet rs = stat.executeQuery(query);

			while (rs.next()) {
				System.out.println("rs.next()== "+ rs.getInt(column));
				toReturn = rs.getInt(column);
			}
			rs.close();
			dbConnection.close();
			System.out.println("Returning: "+toReturn);
			return toReturn;
		}
		catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Saves the nodes to the database.
	 *
	 * @param nodes the HashMap from HyPeerWeb which stores the nodes
	 * 
	 * @pre none
	 * @post all of the nodes are saved into the database
	 * 
	 */
	public void saveToDatabase(HashSet<Node> nodes) {
		clear();
		createWebIdTable();
		for (Node n : nodes) {
			addNodeToDatabase(n);
		}
	}

	/**
	 *	Adds the appropriate relations in the database in order to restore it.
	 *
	 * @param node The node to be added to the SQL tables
	 * 
	 * @pre none
	 * @post All of the connection information is inserted into the tables
	 * 
	 */
	protected static void addNodeToDatabase(Node node) {

		addNodeToWebId(node);
		int fromId = node.getWebId().getValue();
		if(!(node instanceof NodeProxy)) {
			// save neighbors
			for (Node toId : node.getSmallerNeighborsWithoutChildren()) {
				updateOrCreate(SMALLER_WITHOUT_TABLE, fromId, toId.getWebId().getValue());
			}
			for (Node toId : node.getSmallerNeighborsWithChildren()) {
				updateOrCreate(SMALLER_WITH_TABLE, fromId, toId.getWebId().getValue());
			}
			for (Node toId : node.getLargerNeighbors()) {
				updateOrCreate(LARGER_TABLE, fromId, toId.getWebId().getValue());
			}
			// save the sneighbors
			for (Node toId : node.getUpPointers()) {
				updateOrCreate(INVERSE_SURROGATE_NEIGHBOR_TABLE, fromId, toId.getWebId().getValue());
			}
			for (Node toId : node.getDownPointers()) {
				updateOrCreate(SURROGATE_NEIGHBOR_TABLE, fromId, toId.getWebId().getValue());
			}
			// save folds
			if (node.getFold() != null) {
				updateOrCreate(FOLD_TABLE, fromId, node.getFold().getWebId().getValue());
			}
			if (node.getSurrogateFold() != null) {
				updateOrCreate(SURROGATE_FOLD_TABLE, fromId, node.getSurrogateFold().getWebId().getValue());
			}
			if (node.getInverseSurrogateFold()!=null){
				updateOrCreate(INVERSE_SURROGATE_FOLD_TABLE, fromId, node.getInverseSurrogateFold().getWebId().getValue());
				
			}
		
		}

	}

	/**
	 * Updates or creates the relation in the database whos name is passed in.
	 *
	 * @param table 
	 * 				The table name that you are trying to insert the value into.
	 * @param fromId 
	 * 				The web id for the start of the connection
	 * @param toId 
	 * 				The web id for the end of the connection
	 */
	private static void updateOrCreate(String table, int fromId, int toId) {
		//create the select statement:
		String query = "SELECT * FROM " + table + " WHERE ( fromId = " + fromId + " AND toId = " + toId + " );";// OR ( fromId = "+toId+" AND toId = "+fromId+");"; 
		//System.out.println("Query= "+query);
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:"
					+ dbFileName);
			Statement stat = dbConnection.createStatement();

			ResultSet rs = stat.executeQuery(query);
			boolean found = false;

			while (rs.next()) {
				found = true;
			}
			if (!found) {
				//doesn't exist
				PreparedStatement prep = dbConnection.prepareStatement("insert into " + table + " values (?, ?);");

				prep.setString(1, "" + fromId);
				prep.setString(2, "" + toId);
				prep.addBatch();
				dbConnection.setAutoCommit(false);
				prep.executeBatch();
				dbConnection.setAutoCommit(true);

			}

			rs.close();
			dbConnection.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

	// *****************
	//
	// Table Creation/Deletion

	/**
	 * Creates the tables for updating the SQL file.
	 * 
	 * 	@pre the database is uninitialized, or non existant
	 *  @post The database is created or loaded for use with the hyperweb
	 *  
	 */
	private static void loadTables() {
		//initalizes the tables if they don't exist/verifies it
		createTable(SMALLER_WITHOUT_TABLE);
		createTable(SMALLER_WITH_TABLE);
		createTable(LARGER_TABLE);
		createTable(SURROGATE_NEIGHBOR_TABLE);
		createTable(INVERSE_SURROGATE_NEIGHBOR_TABLE);
		createTable(FOLD_TABLE);
		createTable(SURROGATE_FOLD_TABLE);
		createTable(INVERSE_SURROGATE_FOLD_TABLE);

		createWebIdTable();
	}

	/**
	 * Creates a single table.
	 *
	 * @param tableName the table to be created
	 * 
	 * @pre the table is either non existent or not initialized.
	 * @post  the table is either created or initialized.
	 */
	private static void createTable(String tableName) {
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:"
					+ dbFileName);
			Statement stat = dbConnection.createStatement();
			//stat.executeUpdate("drop table if exists neighbor;");
			stat.executeUpdate("create table if not exists " + tableName + " (fromId integer,toId integer);");
			dbConnection.close();

		}
		catch (Exception e) {
			//System.err.println("Couldn't Create " + tableName + " Table");
			e.printStackTrace();
		}
	}


	/**
	 * Checks to see if the given id is in the database.
	 *
	 * @param idToCheck the id to search for if the Node Exists
	 * @return whether or not the given id is in the database
	 */
	private boolean webIdExists(int idToCheck) {
		boolean exists = false;
		//create the select statement:
		String query = "SELECT * FROM webids WHERE webId=" + idToCheck + ";"; 
		//////System.out.println("Query= "+query);
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:" + dbFileName);
			Statement stat = dbConnection.createStatement();

			ResultSet rs = stat.executeQuery(query);

			while (rs.next()) {
				exists = true;
			}
			rs.close();
			dbConnection.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return exists;
	}

	/**
	 * Adds a new webId to the database.
	 *
	 * @param newID the id to be added
	 * @pre the webid database doesn't contain newID
	 * @post the weibid database does contain webID and it's size = +1
	 */
	private static void addNodeToWebId(Node node) {
		//(webId integer, isProxy boolean, locobjID integer, ipAddress string, port integer);
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:" + dbFileName);

			PreparedStatement prep = dbConnection.prepareStatement("insert into webids values (?,?,?,?,?);");
			prep.setString(1, "" + node.getWebId().getValue());
			prep.setBoolean(2, node instanceof NodeProxy);
			if(node instanceof NodeProxy) {
				prep.setInt(3, ((NodeProxy) node).getLocalObjectId().getId());
				prep.setString(4, ((NodeProxy) node).getGlobalObjectId().getMachineAddr());
				prep.setInt(5, ((NodeProxy) node).getGlobalObjectId().getPortNumber().getValue());
			}
			else {
				prep.setInt(3, node.getLocalObjectId().getId());
				prep.setString(4, "null");
				prep.setInt(5, -1);
			}

			prep.addBatch();
			dbConnection.setAutoCommit(false);
			prep.executeBatch();
			dbConnection.setAutoCommit(true);
			////System.out.println("Adding "+newID);


			dbConnection.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Creates the webId table.
	 * createWebIdTable()
	 * @pre The webid table is non existent or un initialized
	 * @post the webid table is created with zero elements.
	 * 
	 */
	private static void createWebIdTable() {
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:"
					+ dbFileName);
			Statement stat = dbConnection.createStatement();
			stat.executeUpdate("create table if not exists webids (webId integer, isProxy boolean, locobjID integer, ipAddress string, port integer);");
			dbConnection.close();

		}
		catch (Exception e) {
			//System.err.println("Couldn't Create webids Table");
			e.printStackTrace();
		}

	}


	/**
	 * Resets a single table.
	 *
	 * @param tableName to be cleared
	 * 
	 * @pre the table passed in exists
	 * @post  the table passed in is empty
	 */	private static void resetTable(String tableName) {
		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:"
					+ dbFileName);
			Statement stat = dbConnection.createStatement();
			stat.executeUpdate("drop table if exists " + tableName + ";");
			stat.executeUpdate("create table if not exists " + tableName + " (fromId integer,toId integer);");
			dbConnection.close();

		}
		catch (Exception e) {
			//System.err.println("Couldn't Create "+tableName+" Table");
			e.printStackTrace();
		}
	}

	/**
	 * Resets all the tables in the database.
	 * 
	 * @pre NONE
	 * @post all the tables exist and are empty
	 */
	public void clear() {
		//System.out.println("Clearing table");
		resetTable(SMALLER_WITH_TABLE);
		resetTable(SMALLER_WITHOUT_TABLE);
		resetTable(LARGER_TABLE);
		resetTable(SURROGATE_NEIGHBOR_TABLE);
		resetTable(INVERSE_SURROGATE_NEIGHBOR_TABLE);

		resetTable(FOLD_TABLE);
		resetTable(SURROGATE_FOLD_TABLE);
		resetTable(INVERSE_SURROGATE_FOLD_TABLE);

		try {
			Class.forName("org.sqlite.JDBC");
			dbConnection = DriverManager.getConnection("jdbc:sqlite:"
					+ dbFileName);
			Statement stat = dbConnection.createStatement();
			stat.executeUpdate("drop table if exists webids;");
			dbConnection.close();
			createWebIdTable();

		}
		catch (Exception e) {
			//System.err.println("Couldn't Create webids Table");
			e.printStackTrace();
		}

		loadTables();
	}

	/**
	 * Sets the current database to the database with the given file name.
	 *
	 * @param newFile the filename of the new database
	 * 
	 * @pre none
	 * @post dbFileName = newFile and all tables are created in the new file
	 * 
	 */
	protected void setDatabaseFile(String newFile) {
		dbFileName = newFile;
		loadTables();
	}
}
